/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package myBeans;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Phu
 */
public class DBProcess {

    Connection conn;
    Statement stmt;

    public DBProcess() {
        try {
            conn = ConnectDBFactory.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<Book> listBook() {
        List<Book> list = new ArrayList<Book>();
        try {
            conn = ConnectDBFactory.getConnection();
            String SQL = "SELECT * FROM Book";
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                list.add(new Book(rs.getString("BookID"), rs.getString("BookTitle"), rs.getString("Author"), rs.getString("Publisher"), rs.getFloat("Price"), rs.getInt("catID"), rs.getString("Cover"), rs.getString("Summary"),rs.getString("Details")));
            }
            System.out.println("price:" + rs.getString("Price"));
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public Book getBook(String id) {

        Book book = new Book();
        try {
             conn=ConnectDBFactory.getConnection(); 
            String SQL = "SELECT * FROM Book WHERE BookID='" + id + "'";
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                book = new Book(rs.getString("BookID"), rs.getString("BookTitle"), rs.getString("Author"), rs.getString("Publisher"), rs.getFloat("Price"), rs.getInt("CatID"), rs.getString("Cover"), rs.getString("Summary"),rs.getString("Details"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return book;
    }
    public List<Category> listCategory() {
        List<Category> list = new ArrayList<Category>();
        try {
            conn = ConnectDBFactory.getConnection();
            String SQL = "SELECT * FROM Category";
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                list.add(new Category(rs.getInt("CatID"),rs.getString("CatName")));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}
